﻿using System;

using System.Linq;
using System.Web;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for EmployeeDAL
/// </summary>

    public class EmployeeDAL:IEmployeeDAL
    {
        public EmployeeDAL()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        #region Handling Employee: Insert,Update,Delete,Active
        /// <summary>
        /// Get all employee
        /// </summary>
        /// <returns></returns>
        public List<Employee> GetAll()
        {
            string strSQL = @"SELECT
	                            [EmployeeID],
	                            [Code],
	                            [FirstName],
	                            [LastName],
	                            [Email],
                                [UserName],
	                            [Active],
                                E.[PermissionID],
                                [Name]
                            FROM
	                            [Employee] E,
                                [Permission] P
                            WHERE 
                                E.[PermissionID]=P.[PermissionID] ORDER BY E.LastUpdate DESC";
            SqlDataReader reader;
            List<Employee> list = null;
            Employee emp;
            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
                if (reader.HasRows)
                {
                    list = new List<Employee>();
                    while (reader.Read())
                    {
                        emp = new Employee();
                        emp.Id = (Guid)reader["EmployeeID"];
                        emp.Code = reader["Code"].ToString();
                        emp.FirstName = reader["FirstName"].ToString();
                        emp.LastName = reader["LastName"].ToString();
                        emp.Email = reader["Email"].ToString();
                        emp.UserName = reader["UserName"].ToString();
                        emp.Active = Boolean.Parse(reader["Active"].ToString());
                        emp.PermissionID = reader["PermissionID"].ToString();
                        emp.Permission = new Permission("", reader["Name"].ToString());
                        list.Add(emp);
                    }
                }

                return list;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Employee GetById(Guid id)
        {
            string strSQL = @"SELECT
	                            [EmployeeID],
	                            [Code],
	                            [FirstName],
	                            [LastName],
	                            [Email],
	                            [IDCard],
	                            [Address],
	                            [HandPhone],
	                            [Gender],
	                            [Position],
	                            [UserName],
	                            [Password],
	                            [BirthDay],
	                            [PermissionID]
                            FROM
	                            [dbo].[Employee]
                            WHERE
	                            [EmployeeID] = @EmployeeID";
            SqlDataReader reader;
            Employee emp = null;

            //parms
            SqlParameter parm = new SqlParameter("@EmployeeID", id);


            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL, parm);

                if (reader.HasRows)
                {
                    reader.Read();
                    emp = new Employee();
                    emp.Id = (Guid)reader["EmployeeID"];
                    emp.Code = reader["Code"].ToString();
                    emp.FirstName = reader["FirstName"].ToString();
                    emp.LastName = reader["LastName"].ToString();
                    emp.Email = reader["Email"].ToString();
                    emp.IDCard = reader["IDCard"].ToString();
                    emp.Address = reader["Address"].ToString();
                    emp.HandPhone = reader["HandPhone"].ToString();
                    emp.Gender = Boolean.Parse(reader["Gender"].ToString());
                    emp.Position = reader["Position"].ToString();
                    emp.UserName = reader["UserName"].ToString();


                    if (!String.IsNullOrEmpty(reader["BirthDay"].ToString()))
                        emp.Birthday = DateTime.Parse(reader["BirthDay"].ToString());
                    emp.Permission = new Permission(reader["PermissionID"].ToString());
                }
                return emp;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// InsertOrUpdate
        /// </summary>
        /// <param name="emp"></param>
        /// <returns></returns>
        public bool InsertOrUpdate(Employee emp)
        {

            #region Sql sentence
            string strSQLInsert = @"INSERT INTO [Employee] (
	                                    [EmployeeID],
	                                    [Code],
	                                    [FirstName],
	                                    [LastName],
	                                    [Email],
	                                    [IDCard],
	                                    [Address],
	                                    [HandPhone],
	                                    [Gender],
	                                    [Position],
	                                    [UserName],
	                                    [Password],
                                        [BirthDay],
	                                    [LastLogin],
	                                    [PermissionID],
                                        [LastUser]
                                    ) VALUES (
	                                    NEWID(),
	                                    @Code,
	                                    @FirstName,
	                                    @LastName,
	                                    @Email,
	                                    @IDCard,
	                                    @Address,
	                                    @HandPhone,
	                                    @Gender,
	                                    @Position,
	                                    @UserName,
	                                    @Password,
                                        @BirthDay,
	                                    @LastLogin,
	                                    @PermissionID,
                                        @LastUser
                                    )";

            string strSQLUpdate = @"UPDATE [Employee] SET
                                        [Code] = @Code,    
	                                    [FirstName] = @FirstName,
	                                    [LastName] = @LastName,
	                                    [Email] = @Email,
	                                    [IDCard] = @IDCard,
	                                    [Address] = @Address,
	                                    [HandPhone] = @HandPhone,
	                                    [Gender] = @Gender,
	                                    [Position] = @Position,
	                                    [BirthDay] = @BirthDay,
	                                    [PermissionID] = @PermissionID,
                                        [LastUpdate] = GetDate()
                                    WHERE
	                                    [EmployeeID] = @EmployeeID";
            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",emp.Id),
                        new SqlParameter("@Code",emp.Code),
                        new SqlParameter("@FirstName",emp.FirstName),
                        new SqlParameter("@LastName",emp.LastName),
                        Utils.NullWrapper("@Email",emp.Email),
                        Utils.NullWrapper("@IDCard",emp.IDCard),
                        Utils.NullWrapper("@Address",emp.Address),
                        Utils.NullWrapper("@HandPhone",emp.HandPhone),
                        new SqlParameter("@Gender",emp.Gender),
                        Utils.NullWrapper("@Position",emp.Position),
                        new SqlParameter("@UserName",emp.UserName),
                        new SqlParameter("@Password",emp.Password),
                        Utils.DateTimeWrapper("@BirthDay", emp.Birthday),
                        new SqlParameter("@LastLogin",DBNull.Value),
                        new SqlParameter("@PermissionID",emp.Permission.PermissionID),
                        new SqlParameter("@LastUser",emp.LastUser)
                    };

            string strSQL;
            //Insert case
            if (emp.Id == Guid.Empty)
            {
                strSQL = strSQLInsert;
            }
            //delete case
            else
            {
                strSQL = strSQLUpdate;
            }

            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// ActiveAction
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool ActiveAction(Employee emp)
        {
            string strSQL = @"UPDATE Employee SET
                                  Active=@active
                              WHERE
                                  EmployeeID=@EmployeeID";

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",emp.Id),
                        new SqlParameter("@active",emp.Active)
                    };

            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// ActiveAction
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckExistCodeInForeignKey(Guid id)
        {
            bool flag = false;
            string strWork = @"SELECT COUNT(EmployeeID)
                                FROM Working  
                              WHERE
                                  EmployeeID=@EmployeeID";
            string strShift = @"SELECT COUNT(EmployeeID)
                                FROM EmployeeShift  
                              WHERE
                                  EmployeeID=@EmployeeID";

            //parm
            SqlParameter parm = new SqlParameter("@EmployeeID", id);

            try
            {
                int work=(int)SqlHelper.ExecuteScalar(CommandType.Text, strWork, parm);
                int shift = (int)SqlHelper.ExecuteScalar(CommandType.Text,strShift, parm);
                if (work>0 ||shift>0)
                    flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        /// <summary>
        /// CheckOldPassWord
        /// </summary>
        /// <param name="emp"></param>
        /// <returns></returns>
        public bool CheckOldPassWord(Employee emp)
        {
            string strEmployee = @"SELECT COUNT(EmployeeID)
                                   FROM 
                                       Employee
                                   WHERE
                                       EmployeeID=@EmployeeID
                                       AND Password=@Password";
            //parm
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",emp.Id),
                        new SqlParameter("@PassWord",emp.Password)
                    };
            try
            {
                return ((int)SqlHelper.ExecuteScalar(CommandType.Text, strEmployee, parms)>0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// ChangePassword
        /// </summary>
        /// <param name="emp">Employee</param>
        /// <returns>bool</returns>
        public bool ChangePassword(Employee emp)
        {
            string strEmployee = @"UPDATE Employee
                                       SET Password=@Password 
                                   WHERE
                                       EmployeeID=@EmployeeID";
            //parm
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",emp.Id),
                        new SqlParameter("@PassWord",emp.Password)
                    };
            try
            {
                return ((int)SqlHelper.ExecuteNonQuery(CommandType.Text, strEmployee, parms) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(Guid id)
        {
            bool flag = false;
            string strSQLEmp = "DELETE FROM Employee WHERE EmployeeID=@EmployeeID";
            string strSQLShift = "DELETE FROM EmployeeShift WHERE EmployeeID=@EmployeeID";
            string strSQLWorking = "DELETE FROM Working WHERE EmployeeID=@EmployeeID";

            //parms
            SqlParameter parm = new SqlParameter("@EmployeeID", id);
            SqlTransaction trans = null;
            try
            {
                //sql connect
                SqlConnection conn = SqlHelper.Connection;
                conn.Open();
                trans = conn.BeginTransaction();

                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQLShift, parm);
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQLWorking, parm);
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQLEmp, parm);
                
                trans.Commit();
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        /// <summary>
        /// CheckIsCodeExist
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckIsCodeExist(string code)
        {
            string strSQL = @"SELECT COUNT(Code) FROM Employee WHERE Code=@Code";

            //parms
            SqlParameter parm = new SqlParameter("@Code", code);

            try
            {
                return (Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parm).ToString()) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// CheckIsCodeExist
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckUserIsExist(string user)
        {
            string strSQL = @"SELECT COUNT(UserName) FROM Employee WHERE UserName=@UserName";

            //parms
            SqlParameter parm = new SqlParameter("@UserName", user);

            try
            {
                return (Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parm).ToString()) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// GetMaxEmpCode
        /// </summary>
        /// <returns></returns>
        public string GetMaxEmpCode() {
            string strSQL = @"SELECT MAX(Code) from Employee";
            try
            {
                return SqlHelper.ExecuteScalar(CommandType.Text, strSQL).ToString();
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion



        #region Handling login: Update last login, Check login
        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateLogin(Guid id)
        {
            string strSQL = @"UPDATE Employee SET
                                  LastLogin=GetDate()
                              WHERE
                                  EmployeeID=@EmployeeID";

            //parm
            SqlParameter parm = new SqlParameter("@EmployeeID", id);
            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parm) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// CheckLogin : Only 3 permissions: Supper Admin,Admin,Manager
        /// </summary>
        /// <param name="emp">Employee</param>
        /// <returns>Employee</returns>
        public Employee CheckLogin(Employee emp)
        {
            string strSQL = @"SELECT
                                EmployeeID,FirstName,LastName,LastLogin,PermissionID,UserName
                            FROM
                                Employee                               
                            WHERE
                                UserName=@username
                                AND Password=@password
                                AND Active=1 
                                AND PermissionID IN ('03','04','05')";

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@username",emp.UserName),
                        new SqlParameter("@password",emp.Password)       
                    };

            try
            {
                SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, strSQL, parms);
                if (dr.HasRows)
                {
                    dr.Read();
                    emp = new Employee();
                    emp.Id = (Guid)dr["EmployeeID"];
                    emp.FirstName = dr["FirstName"].ToString();
                    emp.LastName = dr["LastName"].ToString();

                    //handle last login
                    string last = dr["LastLogin"].ToString();
                    if (!string.IsNullOrEmpty(last))
                        emp.LastLogin = DateTime.Parse(dr["LastLogin"].ToString());

                    emp.Permission = new Permission();
                    emp.Permission.PermissionID = dr["PermissionID"].ToString();
                    emp.UserName = dr["UserName"].ToString();
                }
                return emp;
            }
            catch (Exception)
            {
                throw;
            }
        } 
        #endregion



        #region Handling ShiftGrant
        /// <summary>
        /// Search : use for shiftGrant function
        /// </summary>
        /// <param name="emp"></param>
        /// <returns></returns>
        public List<Employee> Search(Employee emp)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@"SELECT
	                            [EmployeeID],
	                            [Code],
	                            [FirstName],
	                            [LastName],
	                            [Email],
                                [Gender],
                                [Email],
                                [BirthDay],
                                [Position]
                            FROM
	                            [Employee]
                            WHERE 
                               [Active]=1 ");

            #region Handle search keyword
            //if input employee code
            if (!String.IsNullOrEmpty(emp.Code))
                builder.Append("AND Code=@Code ");

            //if input [Ho ten dem]
            if (!String.IsNullOrEmpty(emp.FirstName))
                builder.Append("AND FirstName LIKE '%' + @FirstName + '%' ");

            //if input [Ho ten dem]
            if (!String.IsNullOrEmpty(emp.LastName))
                builder.Append("AND LastName LIKE '%' + @LastName + '%' ");

            //if select Gender
            if (!string.IsNullOrEmpty(emp.SearchGender))
            {
                switch (emp.SearchGender)
                {
                    case "True":
                        builder.Append("AND Gender=1 ");
                        break;
                    case "False":
                        builder.Append("AND Gender=0 ");
                        break;
                    default:
                        break;
                }
            }


            //if input [Chuc vu]
            if (!String.IsNullOrEmpty(emp.Position))
                builder.Append("AND Position=@Position ");
            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@Code",emp.Code),
                        new SqlParameter("@FirstName",emp.FirstName),
                        new SqlParameter("@LastName",emp.LastName),
                        new SqlParameter("@Gender",emp.Gender),
                        new SqlParameter("@Position",emp.Position)
                    };

            SqlDataReader reader;
            List<Employee> list = null;

            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), parms);
                if (reader.HasRows)
                {
                    list = new List<Employee>();
                    while (reader.Read())
                    {
                        emp = new Employee();
                        emp.Id = (Guid)reader["EmployeeID"];
                        emp.Code = reader["Code"].ToString();
                        emp.FirstName = reader["FirstName"].ToString();
                        emp.LastName = reader["LastName"].ToString();
                        emp.Email = reader["Email"].ToString();
                        emp.Gender = Boolean.Parse(reader["Gender"].ToString());
                        if (!string.IsNullOrEmpty(reader["Birthday"].ToString()))
                            emp.Birthday = DateTime.Parse(reader["Birthday"].ToString());
                        emp.Position = reader["Position"].ToString();

                        list.Add(emp);
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return list;
        } 
        #endregion



        #region Handling TimeTracking
        /// <summary>
        /// SearchEmplyeeAndTimeTracking : user for searching employee in timeTracking function
        /// </summary>
        /// <param name="emp"></param>
        /// <param name="work"></param>
        /// <returns></returns>
        public List<Employee> SearchEmplyeeAndTimeTracking(Employee emp, Working work)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@"SELECT
	                            DISTINCT(W.EMPLOYEEID)
	                            ,CODE
	                            ,FIRSTNAME
	                            ,LASTNAME
	                            ,EMAIL
	                            ,GENDER
	                            ,BIRTHDAY
	                            ,POSITION
                            FROM 
	                            WORKING W,EMPLOYEE E
                            WHERE
	                            W.EMPLOYEEID=E.EMPLOYEEID
	                            AND ACTIVE=1 ");

            #region Handle search keyword

            //if input from date
            if (DateTime.MinValue != work.LoginTime)
                builder.Append("AND LogInTime >=@FromDate ");

            //if input to date
            if (DateTime.MinValue != work.LogoutTime)
                builder.Append("AND LogInTime <= @ToDate ");

            //if input [Ho ten dem]
            if (!String.IsNullOrEmpty(emp.FirstName))
                builder.Append("AND FirstName LIKE '%' + @FirstName + '%' ");

            //if input [Ho ten dem]
            if (!String.IsNullOrEmpty(emp.LastName))
                builder.Append("AND LastName LIKE '%' + @LastName + '%' ");

            //if select Gender
            if (!string.IsNullOrEmpty(emp.SearchGender))
            {
                switch (emp.SearchGender)
                {
                    case "True":
                        builder.Append("AND Gender=1 ");
                        break;
                    case "False":
                        builder.Append("AND Gender=0 ");
                        break;
                    default:
                        break;
                }
            }

            //if input [Chuc vu]
            if (!String.IsNullOrEmpty(emp.Position))
                builder.Append("AND Position=@Position ");
            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@Code",emp.Code),
                        new SqlParameter("@FirstName",emp.FirstName),
                        new SqlParameter("@LastName",emp.LastName),
                        new SqlParameter("@Gender",emp.Gender),
                        new SqlParameter("@Position",emp.Position),
                        work.LoginTime==DateTime.MinValue?new SqlParameter("@FromDate",DBNull.Value):new SqlParameter("@FromDate",work.LoginTime),
                        work.LogoutTime==DateTime.MinValue?new SqlParameter("@ToDate",DBNull.Value):new SqlParameter("@ToDate",work.LogoutTime),
                    };

            SqlDataReader reader;
            List<Employee> list = new List<Employee>(); ;

            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), parms);
                if (reader.HasRows)
                {

                    while (reader.Read())
                    {
                        emp = new Employee();
                        emp.Id = (Guid)reader["EmployeeID"];
                        emp.Code = reader["Code"].ToString();
                        emp.FirstName = reader["FirstName"].ToString();
                        emp.LastName = reader["LastName"].ToString();
                        emp.Email = reader["Email"].ToString();
                        emp.Gender = Boolean.Parse(reader["Gender"].ToString());
                        if (!string.IsNullOrEmpty(reader["Birthday"].ToString()))
                            emp.Birthday = DateTime.Parse(reader["Birthday"].ToString());
                        emp.Position = reader["Position"].ToString();

                        list.Add(emp);
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return list;
        }

        /// <summary>
        /// Show detail time tracking, use in timetracking page
        /// </summary>
        /// <param name="employeeID"></param>
        /// <param name="working"></param>
        /// <returns></returns>
        public List<Working> GetWorkingByEmployee(Guid employeeID, Working working)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@"SELECT 
                                 [Code]
                                ,[FirstName]
                                ,[LastName]
                                ,[UserName]
                                ,[Position]
                                ,[WorkingID]
                                ,[ShiftID]
                                ,[ShiftName]
                                ,CONVERT(CHAR(16), [LoginTime], 121) AS [LoginTime]
                                ,CONVERT(CHAR(16), [LogoutTime], 121) AS [LogoutTime]
                            FROM 
                                Working W
                                ,Employee E
                            WHERE 
                                W.EmployeeID=E.EmployeeID
                                AND E.EmployeeID=@EmployeeID ");
            SqlDataReader reader;

            //if input from date
            if (DateTime.MinValue != working.LoginTime)
                builder.Append("AND @FromDate <= LogInTime ");

            //if input to date
            if (DateTime.MinValue != working.LogoutTime)
                builder.Append("AND @ToDate >= LogInTime ");

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@EmployeeID",employeeID),
                        working.LoginTime==DateTime.MinValue?new SqlParameter("@FromDate",DBNull.Value):new SqlParameter("@FromDate",working.LoginTime),
                        working.LogoutTime==DateTime.MinValue?new SqlParameter("@ToDate",DBNull.Value):new SqlParameter("@ToDate",working.LogoutTime)
                    };
            List<Working> list = new List<Working>();
            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, builder.ToString(), parms);

                while (reader.Read())
                {
                    working = new Working();
                    working.Id = new Guid(reader["WorkingID"].ToString());
                    working.ShiftID = byte.Parse(reader["ShiftID"].ToString());
                    working.ShiftName = reader["ShiftName"].ToString();
                    working.LoginTime = Convert.ToDateTime(SqlHelper.CheckDateNull(reader["LoginTime"]));
                    working.LogoutTime = Convert.ToDateTime(SqlHelper.CheckDateNull(reader["LogoutTime"])); 
                    
                    //employee
                    Employee emp = new Employee();
                    emp.Code = reader["Code"].ToString();
                    emp.FirstName = reader["FirstName"].ToString();
                    emp.LastName = reader["LastName"].ToString();
                    emp.UserName = reader["UserName"].ToString();
                    emp.Position = reader["Position"].ToString();
                    //set employee to working
                    working.Employee = emp;

                    list.Add(working);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        } 
        #endregion

        
    }
